package com.fxjava.displayanrdate.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface IssueDataMapper {

    // 通用计数（按时间/型号可选过滤）
    @Select("SELECT COUNT(*) AS issues FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv}))")
    /**
     * 通用计数：统计问题条目总数（可按开始/结束日期与型号集合过滤）
     * 用于：KPI 卡片、过滤回退判断
     */
    Map<String, Object> getIssuesCount(@Param("startDate") String startDate,
                                       @Param("endDate") String endDate,
                                       @Param("modelsCsv") String modelsCsv);

    // 修复率（按时间/型号可选过滤）
    @Select("SELECT ROUND(COUNT(CASE WHEN COALESCE(is_fixed,'') IN ('已修复','Y','Yes','yes','已解决','修复完成') THEN 1 END) * 1.0 / NULLIF(COUNT(*),0), 4) AS fix_rate " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv}))")
    /**
     * 过滤修复率：（已修复/总数），在时间与型号过滤条件下统计
     * 用于：KPI 修复率、分类页仪表盘
     */
    Map<String, Object> getFixRateFiltered(@Param("startDate") String startDate,
                                           @Param("endDate") String endDate,
                                           @Param("modelsCsv") String modelsCsv);

    // 顶部 Top 型号（按过滤）
    @Select("SELECT COALESCE(model,'未知') AS model, COUNT(*) AS count FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY model ORDER BY count DESC LIMIT 1")
    /**
     * Top 型号：在过滤条件下按型号分组计数并取计数最高者
     * 用于：KPI Top 型号
     */
    Map<String, Object> getTopModelFiltered(@Param("startDate") String startDate,
                                            @Param("endDate") String endDate,
                                            @Param("modelsCsv") String modelsCsv);

    @Select("SELECT DISTINCT model FROM issue_data ORDER BY model")
    /**
     * 型号列表：全表去重后按型号名排序
     * 用于：筛选栏型号下拉（FilterBar）
     */
    List<String> getModelList();

    // 时间趋势（日）
    @Select("SELECT DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m-%d') AS date, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m-%d') ORDER BY date")
    /**
     * 时间趋势（日）：按日期（yyyy-MM-dd）聚合计数
     * 用于：趋势分析折线（日粒度）
     */
    List<Map<String, Object>> getTimeSeriesDaily(@Param("startDate") String startDate,
                                                 @Param("endDate") String endDate,
                                                 @Param("modelsCsv") String modelsCsv);

    // 时间趋势（月）
    @Select("SELECT DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') AS month, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') ORDER BY month")
    /**
     * 时间趋势（月）：按月份（yyyy-MM）聚合计数
     * 用于：趋势分析折线（月粒度）
     */
    List<Map<String, Object>> getTimeSeriesMonthly(@Param("startDate") String startDate,
                                                   @Param("endDate") String endDate,
                                                   @Param("modelsCsv") String modelsCsv);

    // 型号-月份时间趋势（聚合：按型号+月份）
    @Select("SELECT COALESCE(model,'未知') AS model, " +
            "DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') AS month, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(model,'未知'), DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') " +
            "ORDER BY month, count DESC")
    /**
     * 型号-时间趋势（月）：按型号+月份聚合计数
     * 用于：型号-时间趋势（period=month）
     */
    List<Map<String, Object>> getModelMonthlyTrendFiltered(@Param("startDate") String startDate,
                                                           @Param("endDate") String endDate,
                                                           @Param("modelsCsv") String modelsCsv);

    // 型号-周时间趋势（聚合：按型号+ISO周）
    @Select("SELECT COALESCE(model,'未知') AS model, " +
            "DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%x-%v') AS week, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(model,'未知'), DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%x-%v') " +
            "ORDER BY week, count DESC")
    /**
     * 型号-时间趋势（周）：按型号+ISO 周聚合计数（%x-%v）
     * 用于：型号-时间趋势（period=week）
     */
    List<Map<String, Object>> getModelWeeklyTrendFiltered(@Param("startDate") String startDate,
                                                          @Param("endDate") String endDate,
                                                          @Param("modelsCsv") String modelsCsv);

    // 型号-日期堆叠基础数据
    @Select("SELECT COALESCE(model,'未知') AS model, DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m-%d') AS date, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(model,'未知'), DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m-%d') " +
            "ORDER BY date, model")
    /**
     * 型号-日期堆叠基础：按型号+日期聚合计数
     * 用于：型号堆叠柱、热力图
     */
    List<Map<String, Object>> getModelStackDataFiltered(@Param("startDate") String startDate,
                                                        @Param("endDate") String endDate,
                                                        @Param("modelsCsv") String modelsCsv);

    // 问题类别饼图
    @Select("SELECT COALESCE(issue_category,'未分类') AS problem_category, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(issue_category,'未分类')")
    /**
     * 问题类别分布：按 issue_category 聚合计数
     * 用于：分类页问题类别饼图
     */
    List<Map<String, Object>> getProblemCategoryDataFiltered(@Param("startDate") String startDate,
                                                             @Param("endDate") String endDate,
                                                             @Param("modelsCsv") String modelsCsv);

    // 初筛饼图（基于 rd_diagnosis 映射为三类：有ANR日志/无ANR日志/硬件问题）
    @Select("SELECT CASE " +
            // 统一去空格并将英文字母转大写，做前缀匹配，兼容“没ANR/无ANR”两种写法
            "WHEN UPPER(TRIM(COALESCE(rd_diagnosis,''))) LIKE '%有ANR%' THEN '有ANR日志' " +
            "WHEN UPPER(TRIM(COALESCE(rd_diagnosis,''))) LIKE '%无ANR%' OR UPPER(TRIM(COALESCE(rd_diagnosis,''))) LIKE '%没ANR%' THEN '无ANR日志' " +
            "ELSE '硬件问题' END AS initial_screening, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY initial_screening")
    /**
     * 初筛结果分布（研发初筛）：按 rd_diagnosis 前缀映射三类后聚合计数
     * 分类：有ANR日志/无ANR日志/硬件问题；沿用时间与型号筛选条件
     * 用于：分类页初筛结果饼图
     */
    List<Map<String, Object>> getInitialScreeningDataFiltered(@Param("startDate") String startDate,
                                                              @Param("endDate") String endDate,
                                                              @Param("modelsCsv") String modelsCsv);

    // 初筛（研发初筛）原始前缀：取 rd_initial_screening 去左空格后的前 12 个字符，交由服务层分类
    @Select("SELECT LEFT(LTRIM(COALESCE(rd_initial_screening,'')), 12) AS prefix " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv}))")
    List<String> getInitialScreeningPrefixesFiltered(@Param("startDate") String startDate,
                                                     @Param("endDate") String endDate,
                                                     @Param("modelsCsv") String modelsCsv);

    // 指定问题类别下的 detail_desc 占比（下钻）
    @Select("SELECT COALESCE(detail_desc,'未知') AS detail, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "AND COALESCE(issue_category,'未知') = #{category} " +
            "GROUP BY COALESCE(detail_desc,'未知') ORDER BY count DESC")
    /**
     * 下钻：指定问题类别下的 detail_desc 占比
     * 用于：分类页扇区点击下钻明细
     */
    List<Map<String, Object>> getCategoryDetailDescDataFiltered(@Param("startDate") String startDate,
                                                                @Param("endDate") String endDate,
                                                                @Param("modelsCsv") String modelsCsv,
                                                                @Param("category") String category);

    // 供应商桑基基础数据
    @Select("SELECT COALESCE(source,'未知来源') AS supplier, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(source,'未知来源')")
    /**
     * 供应商分布：按 source（来源/供应商）聚合计数
     * 用于：供应商桑基图
     */
    List<Map<String, Object>> getSupplierDataFiltered(@Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("modelsCsv") String modelsCsv);

    // 型号问题统计 - Top5（全量）
    @Select("SELECT COALESCE(model,'未知') AS model, COUNT(*) AS count FROM issue_data GROUP BY model ORDER BY count DESC LIMIT 5")
    /**
     * 型号问题统计 Top5：全量按型号聚合并取前 5 名
     * 用于：型号问题统计（柱状图）
     */
    List<Map<String, Object>> getModelProblemStats();

    // information_schema（调试/结构）
    @Select("SELECT COLUMN_NAME AS column_name, DATA_TYPE AS data_type, IS_NULLABLE AS is_nullable, COLUMN_COMMENT AS column_comment " +
            "FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'anr_learning_machine' AND TABLE_NAME = 'issue_data' ORDER BY ORDINAL_POSITION")
    /**
     * 表结构信息：从 information_schema 读取 issue_data 列定义
     * 用于：测试/诊断
     */
    List<Map<String, Object>> getTableColumns();

    // 示例数据
    @Select("SELECT * FROM issue_data LIMIT #{limit}")
    /**
     * 示例数据：读取 issue_data 前 N 条
     * 用于：测试/诊断
     */
    List<Map<String, Object>> getSampleData(int limit);

    // 3.2 修复率分析 - 月度时间序列
    @Select("SELECT DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') AS period, " +
            "SUM(CASE WHEN COALESCE(is_fixed,'') IN ('已修复','Y','Yes','yes','已解决','修复完成','是') THEN 1 ELSE 0 END) AS fixed_count, " +
            "COUNT(*) AS total_count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%Y-%m') ORDER BY period")
    /**
     * 修复率时间序列（月）：每月统计（已修复数，总数）
     * 用于：修复率分析/仪表盘
     */
    List<Map<String, Object>> getFixRateSeriesMonthly(@Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("modelsCsv") String modelsCsv);

    // 3.2 修复率分析 - 周时间序列（ISO 周）
    @Select("SELECT DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%x-%v') AS period, " +
            "SUM(CASE WHEN COALESCE(is_fixed,'') IN ('已修复','Y','Yes','yes','已解决','修复完成','是') THEN 1 ELSE 0 END) AS fixed_count, " +
            "COUNT(*) AS total_count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY DATE_FORMAT(STR_TO_DATE(date_day, '%Y-%m-%d'), '%x-%v') ORDER BY period")
    /**
     * 修复率时间序列（周）：按 ISO 周统计（已修复数，总数）
     * 用于：修复率分析/仪表盘
     */
    List<Map<String, Object>> getFixRateSeriesWeekly(@Param("startDate") String startDate,
                                                     @Param("endDate") String endDate,
                                                     @Param("modelsCsv") String modelsCsv);

    // 3.2 修复率分析 - 全量总体（饼图/仪表盘）
    @Select("SELECT " +
            "SUM(CASE WHEN COALESCE(is_fixed,'') IN ('已修复','Y','Yes','yes','已解决','修复完成','是') THEN 1 ELSE 0 END) AS fixed_count, " +
            "COUNT(*) AS total_count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv}))")
    /**
     * 修复率总体：统计全量（已修复数/总数）
     * 用于：修复占比饼图与仪表盘
     */
    Map<String, Object> getFixRateOverallFiltered(@Param("startDate") String startDate,
                                                  @Param("endDate") String endDate,
                                                  @Param("modelsCsv") String modelsCsv);

    // 3.3 初筛结果与效率 - 复现率分类
    @Select("SELECT CASE WHEN COALESCE(rd_initial_screening,'') LIKE '%无法复现%' THEN '不可复现' ELSE '可复现' END AS type, " +
            "COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY type")
    /**
     * 复现率分类：根据 rd_initial_screening 是否包含“无法复现”划分（可复现/不可复现）
     * 用于：初筛结果饼图
     */
    List<Map<String, Object>> getReproducibilityCountsFiltered(@Param("startDate") String startDate,
                                                               @Param("endDate") String endDate,
                                                               @Param("modelsCsv") String modelsCsv);

    /**
     * 初筛-按 rd_diagnosis 聚合为三类：有ANR日志/无ANR日志/硬件问题（严格过滤）
     */
    @Select("SELECT CASE " +
            "WHEN LEFT(COALESCE(rd_diagnosis,''),3) = '有ANR' THEN '有ANR日志' " +
            "WHEN LEFT(COALESCE(rd_diagnosis,''),3) = '无ANR' THEN '无ANR日志' " +
            "ELSE '硬件问题' END AS diag_category, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY diag_category")
    List<Map<String, Object>> getDiagnosisBucketsFiltered(@Param("startDate") String startDate,
                                                          @Param("endDate") String endDate,
                                                          @Param("modelsCsv") String modelsCsv);

    // 3.3 初筛效率 - 天数直方（按天聚合）
    @Select("SELECT DATEDIFF(STR_TO_DATE(rd_first_review_date, '%Y-%m-%d'), STR_TO_DATE(date_day, '%Y-%m-%d')) AS duration, " +
            "COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "AND STR_TO_DATE(date_day, '%Y-%m-%d') IS NOT NULL " +
            "AND STR_TO_DATE(rd_first_review_date, '%Y-%m-%d') IS NOT NULL " +
            "GROUP BY duration ORDER BY duration")
    /**
     * 初筛效率直方：按提出日至首次评审日的天数差分组计数
     * 用于：效率直方与箱型图估计
     */
    List<Map<String, Object>> getDurationHistogramFiltered(@Param("startDate") String startDate,
                                                           @Param("endDate") String endDate,
                                                           @Param("modelsCsv") String modelsCsv);

    // 型号问题统计（带过滤：时间区间 + 型号集合）- 返回所有型号计数，按数量降序
    @Select("SELECT COALESCE(model,'未知') AS model, COUNT(*) AS count " +
            "FROM issue_data " +
            "WHERE ((#{startDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') >= STR_TO_DATE(#{startDate}, '%Y-%m-%d')) " +
            "AND ((#{endDate} IS NULL) OR STR_TO_DATE(date_day, '%Y-%m-%d') <= STR_TO_DATE(#{endDate}, '%Y-%m-%d')) " +
            "AND ((#{modelsCsv} IS NULL OR #{modelsCsv}='') OR FIND_IN_SET(COALESCE(model,''), #{modelsCsv})) " +
            "GROUP BY COALESCE(model,'未知') ORDER BY count DESC")
    List<Map<String, Object>> getModelProblemStatsFiltered(@Param("startDate") String startDate,
                                                           @Param("endDate") String endDate,
                                                           @Param("modelsCsv") String modelsCsv);
}
